package com.che.software.testato.domain.dao.jdbc.impl;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.che.software.testato.domain.dao.IMatrixItemDAO;
import com.che.software.testato.domain.dao.jdbc.adao.AbstractDAO;
import com.che.software.testato.domain.dao.jdbc.exception.MatrixItemSearchDAOException;
import com.che.software.testato.domain.dao.jdbc.exception.MatrixItemUpdateDAOException;
import com.che.software.testato.domain.entity.MatrixItem;
import com.che.software.testato.domain.entity.search.MatrixItemSearch;

/**
 * JDBC implementation of the DAO interface dedicated to the matrix items
 * management.
 * 
 * @author Clement HELIOU (clement.heliou@che-software.com).
 * @copyright Che Software.
 * @license GNU General Public License.
 * @see AbstractDAO, IMatrixItemDAO.
 * @since July, 2011.
 * 
 *        This file is part of Testato.
 * 
 *        Testato is free software: you can redistribute it and/or modify it
 *        under the terms of the GNU General Public License as published by the
 *        Free Software Foundation, either version 3 of the License, or (at your
 *        option) any later version.
 * 
 *        Testato is distributed in the hope that it will be useful, but WITHOUT
 *        ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
 *        FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License
 *        for more details.
 * 
 *        You should have received a copy of the GNU General Public License
 *        along with Testato. If not, see <http://www.gnu.org/licenses/>.
 * 
 *        Testato's logo is a creation of Arrioch
 *        (http://arrioch.deviantart.com/) and it's distributed under the terms
 *        of the Creative Commons License.
 */
@Repository("matrixItemDAO")
public class MatrixItemDAO extends AbstractDAO implements IMatrixItemDAO {

	/**
	 * Constants.
	 */
	private static final Logger LOGGER = Logger.getLogger(MatrixItemDAO.class);

	/**
	 * Checks if a comparison matrix is ready to be prioritize. In other words,
	 * checks if a matrix item exists without value for a given matrix (i.e. a
	 * given iteration assignment).
	 * 
	 * @author Clement HELIOU (clement.heliou@che-software.com).
	 * @param iterationAssignmentId the given iteration assignment id.
	 * @return true if the matrix is complete, else false.
	 * @since August, 2011.
	 * @throws MatrixItemSearchDAOException if an error occurs during the
	 *         search.
	 */
	@Override
	public boolean isComparisonMatrixComplete(int iterationAssignmentId) throws MatrixItemSearchDAOException {
		LOGGER.debug("isComparisonMatrixComplete(" + iterationAssignmentId + ").");
		Connection connection = null;
		try {
			connection = getDataSource().getConnection();
			return (Boolean) getQueryRunner().query(connection, "SELECT NOT EXISTS( SELECT comparison_matrix_item FROM comparisonMatrixItem WHERE iteration_assignment_id = ? AND value IS NULL ) AS result ", new ScalarHandler("result"), new Object[] { iterationAssignmentId });
		} catch (SQLException e) {
			throw new MatrixItemSearchDAOException(e);
		} finally {
			if (null != connection) {
				DbUtils.closeQuietly(connection);
			}
		}
	}

	/**
	 * Matrix item search from a bean of criterions.
	 * 
	 * @author Clement HELIOU (clement.heliou@che-software.com).
	 * @param searchBean the criterions to use for the search.
	 * @return the resulting object list.
	 * @since July, 2011.
	 * @throws MatrixItemSearchDAOException if an error occurs during the
	 *         search.
	 */
	@Override
	public List<MatrixItem> searchMatrixItem(MatrixItemSearch searchBean) throws MatrixItemSearchDAOException {
		LOGGER.debug("searchMatrixItem().");
		Connection connection = null;
		try {
			connection = getDataSource().getConnection();
			List<Object> params = new ArrayList<Object>();
			return getQueryRunner().query(connection, getMatrixItemSearchQueryFromCriterion(searchBean, params), new BeanListHandler<MatrixItem>(MatrixItem.class), params.toArray());
		} catch (SQLException e) {
			throw new MatrixItemSearchDAOException(e);
		} finally {
			if (null != connection) {
				DbUtils.closeQuietly(connection);
			}
		}
	}

	/**
	 * Updates a matrix item from its object.
	 * 
	 * @author Clement HELIOU (clement.heliou@che-software.com).
	 * @param matrixItemToUpdate the matrix item object to update in the
	 *        database.
	 * @since August, 2011.
	 * @throws MatrixItemUpdateDAOException if an error occurs during the
	 *         update.
	 */
	@Override
	public void updateMatrixItem(MatrixItem matrixItemToUpdate) throws MatrixItemUpdateDAOException {
		LOGGER.debug("updateMatrixItem(" + matrixItemToUpdate.getIterationAssignmentId() + ").");
		Connection connection = null;
		try {
			connection = getDataSource().getConnection();
			List<Object> params = new ArrayList<Object>();
			getQueryRunner().update(connection, getMatrixItemUpdateQueryFromCriterion(matrixItemToUpdate, params), params.toArray());
		} catch (SQLException e) {
			throw new MatrixItemUpdateDAOException(e);
		} finally {
			if (null != connection) {
				DbUtils.closeQuietly(connection);
			}
		}
	}

	/**
	 * Recovery of the matrix item search query from criterion.
	 * 
	 * @author Clement HELIOU (clement.heliou@che-software.com).
	 * @param searchBean the object containing the criterions.
	 * @param params the parameters list corresponding to the built query.
	 * @return the built query.
	 * @since July, 2011.
	 */
	private String getMatrixItemSearchQueryFromCriterion(MatrixItemSearch searchBean, List<Object> params) {
		LOGGER.debug("getMatrixItemSearchQueryFromCriterion().");
		setWhereClauseEnabled(false);
		StringBuilder sBuilder = new StringBuilder("SELECT iteration_assignment_id AS iterationAssignmentId, first_script AS firstScript, second_script AS secondScript, comparison_matrix_item AS comparisonMatrixItemId, value, COALESCE(f.label,'') AS firstScriptLabel, COALESCE(s.label,'') AS secondScriptLabel FROM comparisonMatrixItem JOIN script f ON(f.script_id = first_script) JOIN script s ON(s.script_id = second_script) ");
		if (null != searchBean && null != searchBean.getIterationAssignmentId() && 0 != searchBean.getIterationAssignmentId()) {
			sBuilder.append(getWhereClauseBegin());
			sBuilder.append("iteration_assignment_id = ? ");
			params.add(searchBean.getIterationAssignmentId());
		}
		if (null != searchBean && null != searchBean.getFirstScript() && 0 != searchBean.getFirstScript()) {
			sBuilder.append(getWhereClauseBegin());
			sBuilder.append("first_script = ? ");
			params.add(searchBean.getFirstScript());
		}
		sBuilder.append("ORDER BY iteration_assignment_id, first_script, second_script ");
		return sBuilder.toString();
	}

	/**
	 * Recovery of the matrix item update query from criterion.
	 * 
	 * @author Clement HELIOU (clement.heliou@che-software.com).
	 * @param matrixItemToUpdate the object containing the criterions.
	 * @param params the parameters list corresponding to the built query.
	 * @return the built query.
	 * @since August, 2011.
	 */
	private String getMatrixItemUpdateQueryFromCriterion(MatrixItem matrixItemToUpdate, List<Object> params) {
		LOGGER.debug("getMatrixItemUpdateQueryFromCriterion().");
		setSetClauseEnabled(false);
		StringBuilder sBuilder = new StringBuilder("UPDATE comparisonMatrixItem ");
		if (null != matrixItemToUpdate && null != matrixItemToUpdate.getValue() && 0 != matrixItemToUpdate.getValue()) {
			sBuilder.append(getSetClauseBegin());
			sBuilder.append("value = ? ");
			params.add(matrixItemToUpdate.getValue());
		}
		sBuilder.append("WHERE comparison_matrix_item = ? ");
		params.add(matrixItemToUpdate.getComparisonMatrixItemId());
		return sBuilder.toString();
	}
}